"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : describe sql
Case Name   : desc/describe查询执行计划，结合where条件查询
Create At   : 2023-01-13
@zou_jialiang0501162244
Description :
    1.创建两张表插入数据
    2.desc/describe查询执行计划，where 比较操作符，查询plan_table表
    3.desc/describe查询执行计划，where [not] in
    4.desc/describe查询执行计划，where [not]exists
    5.清理环境
Expect      :
    1.成功
    2.执行计划存入plan_table中不打印出来
    3.打印执行计划
    4.打印执行计划
    5.成功
History     :
"""

import os
import unittest

from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant


class Describe(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.tb1_name = 't_describe_0065_01'
        self.tb2_name = 't_describe_0065_02'
        self.db_name = 'db_describe_0065'
        self.log.info('----创建兼容B库----')
        create_db = f"drop database if exists {self.db_name};" \
                    f"create database {self.db_name} dbcompatibility='B';"
        res_createdb = self.pri_sh.execut_db_sql(create_db)
        self.log.info(res_createdb)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, res_createdb,
                      f"创建兼容B库失败")

    def test_describe_sql(self):
        text1 = '-----step1:创建两张表并插入数据;expect:成功-----'
        self.log.info(text1)
        create_tb = f'''drop table if exists {self.tb1_name},{self.tb2_name};
        create table {self.tb1_name}(
        name varchar(10), stu_id integer not null, score int);
        create table {self.tb2_name}(
        cname varchar(10), cid varchar(5) not null, 
        num int, sname varchar(10));
        insert into {self.tb1_name} values
        ('张三', 1, 50), ('李四', 2, 55), ('王五', 3, 30);
        insert into {self.tb2_name} values
        ('数学', '01', 50, '张三'), ('语文', '02', 55, '李四'), 
        ('英语', 3, 30, '王五'), ('物理', 4, 50, '杨六');'''
        res_createtb = self.pri_sh.execut_db_sql(create_tb, dbname=self.db_name)
        self.log.info(res_createtb)
        self.assertEqual(res_createtb.count(self.constant.CREATE_TABLE_SUCCESS), 
                         2, f'建表失败:{text1}')
        self.assertEqual(res_createtb.count(self.constant.INSERT_SUCCESS_MSG), 
                         2, f'插入数据失败:{text1}')

        text2 = '-----step2:desc/describe查询执行计划，where比较操作符，' \
                '查询plan_table表;expect:计划存入plan_table中，不打印-----'
        self.log.info(text2)
        desc_sql = f'''
        desc plan for select * from {self.tb1_name} where stu_id>50;
        describe plan for select * from {self.tb1_name} where stu_id>=50;
        desc plan for select * from {self.tb1_name} where stu_id<50;
        desc plan for select * from {self.tb1_name} where stu_id<=50;
        describe plan for select * from {self.tb1_name} where stu_id<>50;
        describe plan for select * from {self.tb1_name} where stu_id=50;
        select id, operation, options, object_name, object_type, 
        object_owner, projection from plan_table order by 1;'''
        res_1 = self.pri_sh.execut_db_sql(desc_sql, dbname=self.db_name)
        self.log.info(res_1)
        self.assertEqual(res_1.count('EXPLAIN SUCCESS'), 6,
                         f'执行失败:{text2}')
        self.assertEqual(
            res_1.count(f'TABLE ACCESS | SEQ SCAN | {self.tb1_name}'),
            6, f'执行失败:{text2}')

        text3 = '-----step3:desc/describe查询执行计划，where [not] in;' \
                'expect:打印执行计划-----'
        self.log.info(text3)
        desc_sql2 = f'''desc performance select * from {self.tb1_name} 
        where name in (select sname from {self.tb2_name} where num>=50);
        describe performance select * from {self.tb1_name} where name in 
        (select sname from {self.tb2_name} where num>=50);
        desc select * from {self.tb1_name} where name not in 
        (select sname from {self.tb2_name} where num<50);
        describe select * from {self.tb1_name} where name not in 
        (select sname from {self.tb2_name} where num<50);'''
        res_2 = self.pri_sh.execut_db_sql(desc_sql2, dbname=self.db_name)
        self.log.info(res_2)
        self.assertEqual(res_2.count('Hash Join'), 2, f'执行失败:{text3}')
        self.assertEqual(res_2.count('Total runtime'), 2, f'执行失败:{text3}')
        self.assertEqual(res_2.count('Output:'), 10, f'执行失败:{text3}')
        self.assertEqual(res_2.count('Buffers:'), 10, f'执行失败:{text3}')
        self.assertEqual(res_2.count('Nested Loop Anti Join'), 2,
                         f'执行失败:{text3}')
        self.assertEqual(res_2.count('Filter: (num < 50)'), 2,
                         f'执行失败:{text3}')

        text4 = '-----step4:desc/describe查询执行计划，where [not] exists;' \
                'expect:打印执行计划-----'
        self.log.info(text4)
        desc_sql3 = f'''desc verbose select name, score from {self.tb1_name} 
        where exists (select sname from {self.tb2_name} where num>50) 
        or score>=50;
        describe verbose select name, score from {self.tb1_name} 
        where exists (select sname from {self.tb2_name} where num>50) 
        or score>=50;
        desc verbose select name, score from {self.tb1_name} 
        where not exists (select sname from {self.tb2_name} where num>80);
        describe verbose select name, score from {self.tb1_name} 
        where not exists (select sname from {self.tb2_name} where num>80);'''
        res_3 = self.pri_sh.execut_db_sql(desc_sql3, dbname=self.db_name)
        self.log.info(res_3)
        self.assertEqual(res_3.count('Seq Scan on'), 8, f'执行失败:{text4}')
        self.assertEqual(res_3.count('Output:'), 6, f'执行失败:{text4}')
        self.assertEqual(res_3.count('One-Time Filter: (NOT $0)'), 2,
                         f'执行失败:{text4}')

    def tearDown(self):
        text5 = '-----step5:清理环境;expect:成功-----'
        self.log.info(text5)
        drop_db = self.pri_sh.execut_db_sql(f"drop database {self.db_name};")
        self.log.info(drop_db)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_db,
                      f'执行失败:{text5}')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
